<%@ page language="java" contentType="application/vnd.ms-excel; charset=UTF-8"%>
<%@ page language="java" import="com.base.myproject.client.jdbc.*" %>
<%@ page language="java" import="com.base.myproject.server.*" %>
<%@ page language="java" import="com.base.myproject.client.jdbc.DataSet" %>
<%@ page language="java" import="com.base.myproject.server.tools.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>宿舍报表</title>
<%
	String where = request.getParameter("where");
	String excel = request.getParameter("excel");
	if (excel != null && excel.equals("1")) {
		response.setHeader("Content-disposition","attachment; filename=hostle.xls");
	}
	
	StringBuilder sql = new StringBuilder();
	sql.append("select hr_hostel.code, bedNumber, isnull(employno, '') as employno, isnull(bname, '') as bname, isnull(system, '') as system, " +
			"isnull(departmentName,'') as departmentName, isnull(bcodeRecord.sex, '') sex, liveDate, leaveDate from hr_Hostel left join (" +
			"select 1 as bedNumber union select 2 union select 3 union select 4 union select 5 union select 6"+
			") as bed on bedNumber <= bednum left join ("+
			"select employno, bcode.bname, (select name from systeminfo where id = systemid) as system, "+
			"(select name from systeminfo where id = up_department) as departmentName, sex, livedate, leavedate, code, bednum "+
			"from bcode, bcode_job, hr_hostel_bcode "+
			"where bcode.bcode = bcode_job.bcode and bcode.bcode = hr_hostel_bcode.bcode");
	sql.append(") as bcodeRecord on bcodeRecord.code = hr_Hostel.code and bcodeRecord.bedNum = bedNumber where 1=1");
	if (where != null)		
		sql.append(where);
	sql.append(" order by hr_hostel.code asc, bedNumber asc");
	
	DataSetTool DST = new DataSetTool();
	DataSet hostleDetail = DST.getDataSet(sql.toString());
%>
</head>
<body>
<table width="100%" style="border-collapse: collapse" border="1"  cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
	<tr  bgcolor="#e8e8e0" align="center">
		<td>房号</td>
		<td>床号</td>
		<td>姓名</td>
		<td>工号</td>
		<td>部门</td>
		<td>分部</td>
		<td>性别</td>
		<td>入住日期</td>
		<td>退住日期</td>
	</tr>
	<% for (int i=0; i<hostleDetail.getRowCount(); i++) { %>
		<tr align="center">
			<td><%= hostleDetail.getValue(i, "code") %></td>
			<td><%= hostleDetail.getValue(i, "bedNumber") %></td>
			<td><%= hostleDetail.getValue(i, "bname") %></td>
			<td><%= hostleDetail.getValue(i, "employno") %></td>
			<td><%= hostleDetail.getValue(i, "departmentName") %></td>
			<td><%= hostleDetail.getValue(i, "system") %></td>
			<td><%= hostleDetail.getValue(i, "sex") %></td>
			<td><%= hostleDetail.getValue(i, "liveDate") == null ? "" : hostleDetail.getValue(i, "liveDate") %></td>
			<td><%= hostleDetail.getValue(i, "leaveDate") == null ? "" : hostleDetail.getValue(i, "leaveDate") %></td>
		</tr>
	<% } %>
	
</table>
</body>
</html>